﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using TSqlServer;
public partial class Adcustomer_TAdCustomer : System.Web.UI.Page
{
    public int pagesize = 20;
    public string pager = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind();
            DBddl();
        }
        if (Request["opration"] != null)
            switch (Request["opration"].ToString())
            {
                case "edit":
                    BindInput();
                    break;
                case "ddlbind":
                    DBddl();
                    break;
            }
        
    }

    public void Bind()
    {
        DBddl();
        string sSql = "";
        int page = 1;
        if (Request["page"] != null)
        {
            page = int.Parse(Request["page"].ToString());
            //sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,* from t_ad_customer left join dbo.t_ad_setup_info2 on t_ad_setup_info2.infoid=t_ad_customer.area or t_ad_setup_info2.infoid=t_ad_customer.cgroup or t_ad_setup_info2.infoid=t_ad_customer.clevel and dbo.t_ad_setup_info2.isshow = 1 ) select  * from a where num between " + ((page - 1) * pagesize + 1) + " and " + ((page - 1) * pagesize + pagesize);
            sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,customer_id,[name],name2,aa.infoname as area,b.infoname as cgroup,c.infoname as clevel,www,bankname,account from t_ad_customer left join dbo.t_ad_setup_info2 as aa on aa.infoid=t_ad_customer.area and aa.isshow = 1 left join dbo.t_ad_setup_info2 as b on b.infoid=t_ad_customer.cgroup and b.isshow = 1 left join dbo.t_ad_setup_info2 as c on c.infoid=t_ad_customer.clevel  and c.isshow = 1 ) select * from a where num between " + ((page - 1) * pagesize + 1) + " and " + ((page - 1) * pagesize + pagesize);
        }
        else
        {
            //sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,* from t_ad_customer left join dbo.t_ad_setup_info2 on t_ad_setup_info2.infoid=t_ad_customer.area or t_ad_setup_info2.infoid=t_ad_customer.cgroup or t_ad_setup_info2.infoid=t_ad_customer.clevel and dbo.t_ad_setup_info2.isshow = 1 ) select * from a where num between 1 and " + pagesize;
            sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,customer_id,[name],name2,aa.infoname as area,b.infoname as cgroup,c.infoname as clevel,www,bankname,account from t_ad_customer left join dbo.t_ad_setup_info2 as aa on aa.infoid=t_ad_customer.area and aa.isshow = 1 left join dbo.t_ad_setup_info2 as b on b.infoid=t_ad_customer.cgroup and b.isshow = 1 left join dbo.t_ad_setup_info2 as c on c.infoid=t_ad_customer.clevel  and c.isshow = 1 ) select * from a where num between 1 and " + pagesize;
        }
        TSqlDBReader myReader = new TSqlDBReader(sSql);
        this.Repeater1.DataSource = myReader.FDataReader;
        this.Repeater1.DataBind();
        if (myReader.FDataReader.HasRows)
        {
            int countnum = TSqlDB.ExecuteScalar(sSql);
            if (countnum % pagesize > 0)
                pager = GenPagebar(page, countnum / pagesize + 1);
            else
                pager = GenPagebar(page, countnum / pagesize);
        }

    }

    private void BindInput()
    {

        if (Request["id"] != null)
        {
            TAdCustomer ta = new TAdCustomer(Request["id"].ToString());
            ta.Select();
            Response.Clear();
            Response.Write(ta.CustomerId + "," + ta.Name + "," + ta.Name2 + "," + ta.Addr + "," + ta.Cgroup + "," + ta.Clevel + "," + ta.Addr + "," + ta.Www+","+ta.BankName+","+ta.Account+","+ta.Notice+","+ta.Inputer+","+ta.InputTime.ToString());
            Response.Flush();
            Response.Close();
        }

    }

    public string GenPagebar(int PageNum, int PageCounts)
    {
        string result = "<div class=\"pagebox\">";


        if (PageCounts > 1)
        {
            if (PageNum == 1)//第一页
            {
                result += "<span class=\"pagebox_num_nonce\"><a>首页</a></span>";
                for (int i = 1; i <= PageCounts; i++)
                {
                    if (i != PageNum)
                    {
                        result += "<span class=\"pagebox_num\"><a href='TAdCustomer.aspx?opration=page&page=" + i.ToString() + "'\">" + i.ToString() + "</a></span>";
                    }
                    else
                    {
                        result += "<span class=\"pagebox_num_nonce\"><a>" + i.ToString() + "</a></span>";
                    }
                }
                result += "<span class=\"pagebox_pre_nolink\">上一页</span>";
                result += "<span class=\"pagebox_next\"><a href='TAdCustomer.aspx?opration=page&page=2'\">下一页</a></span>";
                result += "<span class=\"pagebox_num\"><a href='TAdCustomer.aspx?opration=page&page=" + PageCounts.ToString() + "'\">尾页</a></span>";
            }
            if ((PageNum > 1) && (PageNum < PageCounts))
            {
                result += "<span class=\"pagebox_num\"><a href='TAdCustomer.aspx?opration=page&page=1'\">首页</a></span>";
                for (int i = 1; i <= PageCounts; i++)
                {
                    if (i != PageNum)
                    {
                        result += "<span class=\"pagebox_num\"><a href='TAdCustomer.aspx?opration=page&page=" + i.ToString() + "'\">" + i.ToString() + "</a></span>";
                    }
                    else
                    {
                        result += "<span class=\"pagebox_num_nonce\"><a>" + i.ToString() + "</a></span>";
                    }
                }
                result += "<span class=\"pagebox_pre\"><a href='TAdCustomer.aspx?opration=page&page=" + (PageNum - 1).ToString() + "'\">上一页</a></span>";
                result += "<span class=\"pagebox_next\"><a href='TAdCustomer.aspx?opration=page&page=" + (PageNum + 1).ToString() + "'\">下一页</a></span>";
                result += "<span class=\"pagebox_num\"><a href='TAdCustomer.aspx?opration=page&page=" + PageCounts.ToString() + "'\">尾页</a></span>";
            }

            if (PageNum == PageCounts)//最后一页
            {
                result += "<span class=\"pagebox_num\"><a href='TAdCustomer.aspx?opration=page&page=1'\">首页</a></span>";
                for (int i = 1; i <= PageCounts; i++)
                {
                    if (i != PageNum)
                    {
                        result += "<span class=\"pagebox_num\"><a href='TAdCustomer.aspx?opration=page&page=" + i.ToString() + "'\">" + i.ToString() + "</a></span>";
                    }
                    else
                    {
                        result += "<span class=\"pagebox_num_nonce\"><a>" + i.ToString() + "</a></span>";
                    }
                }
                result += "<span class=\"pagebox_pre\"><a href='TAdCustomer.aspx?opration=page&page=" + (PageCounts - 1).ToString() + "'\">上一页</a></span>";
                result += "<span class=\"pagebox_next_nolink\">下一页</span>";
                result += "<span class=\"pagebox_num_nonce\"><a>尾页</a></span>";
            }
        }
        else
        {
            result += "<span class=\"pagebox_num_nonce\"><a>首页</a></span>";
            result += "<span class=\"pagebox_num_nonce\"><a>1</a></span>";
            result += "<span class=\"pagebox_pre_nolink\">上一页</span>";
            result += "<span class=\"pagebox_next_nolink\">下一页</span>";
            result += "<span class=\"pagebox_num_nonce\"><a>尾页</a></span>";
        }

        result += "<span style=\"padding-top: 4px;\">&nbsp;(每页显示" + pagesize.ToString() + "行)</span>";
        result += "</div>";

        return result;
    }
    protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
    {
        string id = e.CommandArgument as string;

        if (e.CommandName == "Del")
        {
            TData.DeleteByID(id, "t_ad_customer", "customer_id");
        }
        Bind();
    }
    protected void btn_insert_Click(object sender, EventArgs e)
    {
        try
        {
            string sql = sql = "insert into dbo.t_ad_customer(customer_id,name,name2,area,cgroup,clevel,addr,zip,www,bankname,account) values('" + this.txt_id.Text.Trim() + "','" + this.txt_name.Text.Trim() + "','" + this.txt_name2.Text.Trim() + "','" + this.ddl_area.SelectedValue + "','" + this.ddl_cgroup.SelectedValue + "','" + this.ddl_clevel.SelectedValue + "','" + this.txt_addr.Text.Trim() + "','" + this.txt_zip.Text.Trim() + "','" + this.txt_www.Text.Trim() + "','" + this.txt_bankname.Text.Trim() + "','" + this.txt_number.Text.Trim() + "')";
            TSqlDB tsql = new TSqlDB();
            tsql.Execute(sql);
            //this.Response.Write("<script>");
            //this.Response.Write("alert('添加成功！')");
            //this.Response.Write("</script>");
            this.txt_id.Text = "";
            this.txt_name.Text = "";
            this.txt_name2.Text = "";
            this.txt_addr.Text = "";
            this.txt_zip.Text = "";
            this.txt_www.Text = "";
            this.txt_number.Text = "";
            this.txt_bankname.Text = "";
        }
        catch (Exception ex)
        {
            this.Response.Write("<script>alert('编号以存在，请重新编写！')</script>");
        }
        Bind();

    }
    protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
    {
        Bind();
    }
   
    protected void Button1_Click(object sender, EventArgs e)
    {
        string sql = "";
        this.lbl_id.Text = this.HiddenField1.Value;

        sql = "update t_ad_customer set [name] ='" + this.txt_uname.Text.Trim() + "', name2 ='" + this.txt_uname2.Text.Trim() + "', area='" + this.ddl_uarea.SelectedValue + "', cgroup ='" + this.ddl_ucgroup.SelectedValue + "', clevel ='" + this.ddl_uclevel.SelectedValue + "',addr='" + this.txt_uaddr.Text.Trim() + "',zip='" + this.txt_uzip.Text.Trim() + "',www='" + this.txt_uwww.Text.Trim() + "',bankname='" + this.txt_ubankname.Text.Trim() + "',account='" + this.txt_unumber.Text.Trim() + "' where customer_id='" + lbl_id.Text + "'";
        TSqlDB tsql = new TSqlDB();
        tsql.Execute(sql);
        //this.Response.Write("<script type='text/javascript'>alert('修改成功！')</script>");

        Bind();
    }
    protected void btn_look_Click(object sender, EventArgs e)
    {
        if (this.txt_SelectId.Text.Trim() == "" && this.txt_SelectName.Text.Trim() == "")
        {
            Bind();
        }
        else
        {
            string sSql = "select * from t_ad_sales where salescode like '%" + this.txt_SelectId.Text + "%' and salesname like'%" + this.txt_SelectName.Text + "%'";
            if (this.txt_SelectName.Text == "")
            {
                this.txt_SelectName.Text = " ";
            }
            if (this.txt_SelectId.Text == "")
            {
                this.txt_SelectId.Text = " ";
            }
            sSql = "select * from dbo.t_ad_customer where customer_id like '%"+ this.txt_SelectId.Text.Trim() + "%' and [name] like'%" + this.txt_SelectName.Text.Trim() + "%'";

            int page = 1;
            if (Request["page"] != null)
            {
                page = int.Parse(Request["page"].ToString());
                //sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,* from t_ad_customer ) select  * from a where num between " + ((page - 1) * pagesize + 1) + " and " + ((page - 1) * pagesize + pagesize) + " and customer_id like '%" + this.txt_SelectId.Text.Trim() + "%' and [name] like'%" + this.txt_SelectName.Text.Trim() + "%'";
                sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,customer_id,[name],name2,aa.infoname as area,b.infoname as cgroup,c.infoname as clevel,www,bankname,account from t_ad_customer left join dbo.t_ad_setup_info2 as aa on aa.infoid=t_ad_customer.area and aa.isshow = 1 left join dbo.t_ad_setup_info2 as b on b.infoid=t_ad_customer.cgroup and b.isshow = 1 left join dbo.t_ad_setup_info2 as c on c.infoid=t_ad_customer.clevel  and c.isshow = 1 ) select * from a where num between " + ((page - 1) * pagesize + 1) + " and " + ((page - 1) * pagesize + pagesize)+ " and customer_id like '%" + this.txt_SelectId.Text.Trim() + "%' and [name] like'%" + this.txt_SelectName.Text.Trim() + "%'";
                //sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,* from t_ad_customer left join dbo.t_ad_setup_info2 on t_ad_setup_info2.infoid=t_ad_customer.area and dbo.t_ad_setup_info2.isshow = 1 ) select  * from a where num between " + ((page - 1) * pagesize + 1) + " and " + ((page - 1) * pagesize + pagesize) + " and customer_id like '%" + this.txt_SelectId.Text.Trim() + "%' and [name] like'%" + this.txt_SelectName.Text.Trim() + "%'";
                       //"with a as ( select (select count(*) from t_ad_sales)    as countnum, row_number() over(order by intime desc) as num,*     from t_ad_sales left join dbo.t_ad_setup_info2 on t_ad_setup_info2.infoid=t_ad_sales.jobposi and dbo.t_ad_setup_info2.isshow = 1 ) select  * from a where num between " + ((page - 1) * pagesize + 1) + " and " + ((page - 1) * pagesize + pagesize);
            }
            else
            {
                //sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,* from t_ad_customer ) select * from a where num between 1 and " + pagesize + " and customer_id like '%" + this.txt_SelectId.Text.Trim() + "%' and [name] like'%" + this.txt_SelectName.Text.Trim() + "%'";
                //sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,* from t_ad_customer left join dbo.t_ad_setup_info2 on t_ad_setup_info2.infoid=t_ad_customer.area and dbo.t_ad_setup_info2.isshow = 1 ) select * from a where num between 1 and " + pagesize + " and customer_id like '%" + this.txt_SelectId.Text.Trim() + "%' and [name] like'%" + this.txt_SelectName.Text.Trim() + "%'";
                sSql = "with a as ( select (select count(*) from t_ad_customer) as countnum, row_number() over(order by input_time desc) as num,customer_id,[name],name2,aa.infoname as area,b.infoname as cgroup,c.infoname as clevel,www,bankname,account from t_ad_customer left join dbo.t_ad_setup_info2 as aa on aa.infoid=t_ad_customer.area and aa.isshow = 1 left join dbo.t_ad_setup_info2 as b on b.infoid=t_ad_customer.cgroup and b.isshow = 1 left join dbo.t_ad_setup_info2 as c on c.infoid=t_ad_customer.clevel  and c.isshow = 1 ) select * from a where num between 1 and " + pagesize + " and customer_id like '%" + this.txt_SelectId.Text.Trim() + "%' and [name] like'%" + this.txt_SelectName.Text.Trim() + "%'";
                       //"with a as ( select (select count(*) from t_ad_sales) as countnum, row_number() over(order by intime desc) as num,*        from t_ad_sales left join dbo.t_ad_setup_info2 on t_ad_setup_info2.infoid=t_ad_sales.jobposi and dbo.t_ad_setup_info2.isshow = 1 ) select * from a where num between 1 and " + pagesize;
            }
            TSqlDBReader myReader = new TSqlDBReader(sSql);
            this.Repeater1.DataSource = myReader.FDataReader;
            this.Repeater1.DataBind();
            if (myReader.FDataReader.HasRows)
            {
                int countnum = TSqlDB.ExecuteScalar(sSql);
                if (countnum % pagesize > 0)
                    pager = GenPagebar(page, countnum / pagesize + 1);
                else
                    pager = GenPagebar(page, countnum / pagesize);
            }


            //TSqlDBReader myReader = new TSqlDBReader(sql);
            //this.Repeater1.DataSource = myReader.FDataReader;
            //this.Repeater1.DataBind();
            this.txt_SelectId.Text = "";
            this.txt_SelectName.Text = "";


            //BindPageData();
            //Bind();
            //getpager();
        }
    }
    private void DBddl()
    {
        string sql = "select infoid,infoname from t_ad_setup_info2 where typeid = 'S02'";
        TSqlDBReader myReader = new TSqlDBReader(sql);

        ddl_area.DataValueField = "infoid";
        ddl_area.DataTextField = "infoname";
        ddl_area.DataSource = myReader.FDataReader;
        ddl_area.DataBind();

        TSqlDBReader myReader2 = new TSqlDBReader(sql);
        ddl_uarea.DataValueField = "infoid";
        ddl_uarea.DataTextField = "infoname";
        ddl_uarea.DataSource = myReader2.FDataReader;
        ddl_uarea.DataBind();

        string sql1 = "select infoid,infoname from t_ad_setup_info2 where typeid = 'S03'";
        TSqlDBReader myReader3 = new TSqlDBReader(sql1);

        ddl_cgroup.DataValueField = "infoid";
        ddl_cgroup.DataTextField = "infoname";
        ddl_cgroup.DataSource = myReader3.FDataReader;
        ddl_cgroup.DataBind();

        TSqlDBReader myReader4 = new TSqlDBReader(sql1);
        ddl_ucgroup.DataValueField = "infoid";
        ddl_ucgroup.DataTextField = "infoname";
        ddl_ucgroup.DataSource = myReader4.FDataReader;
        ddl_ucgroup.DataBind();

        string sql2 = "select infoid,infoname from t_ad_setup_info2 where typeid = 'S04'";
        TSqlDBReader myReader5 = new TSqlDBReader(sql2);

        ddl_clevel.DataValueField = "infoid";
        ddl_clevel.DataTextField = "infoname";
        ddl_clevel.DataSource = myReader5.FDataReader;
        ddl_clevel.DataBind();

        TSqlDBReader myReader6 = new TSqlDBReader(sql2);
        ddl_uclevel.DataValueField = "infoid";
        ddl_uclevel.DataTextField = "infoname";
        ddl_uclevel.DataSource = myReader6.FDataReader;
        ddl_uclevel.DataBind();

    }

    protected void Button3_Click(object sender, EventArgs e)
    {
        Bind();
    }
}
